
const Service = require('egg').Service;

class HomeService extends Service {
  async add({ username, address, age, gender, birth }) {
    // console.log('增加接口:--', username)
    const sql = `insert into usersinfo (username, age, gender, address, birth) values(?,?,?,?,?)`;
    const data = await this.ctx.app.mysql.query(sql, [username, age, gender, address, birth])
    return {
      id: data.insertId,
      code: 0,
      msg: 'success'
    }
  }

  async page({ page = 0, size }) { // userinfo
    const sqlTotal = `select count(id) as num from usersinfo`
    const sql = `select * from usersinfo limit ${size} offset ${page * size};`
    const total = await this.ctx.app.mysql.query(sqlTotal)
    const data = await this.ctx.app.mysql.query(sql)
    // console.log('总数：', total)
    return {
      total: total[0].num,
      data,
    }
  }

  async update({ id, username, age, gender, address, birth }) {
    const sql = `update usersinfo set username=? ,age=?,gender=?,address=?, birth=? where id=${id}`;
    const data = await this.ctx.app.mysql.query(sql, [username, age, gender, address, birth]);
    return { id: data.affectedRows, code: 0, msg: 'success' }
  }

  // 删除 和批量删除
  async delete({ id }) { // [1,2,3,4,5] [].join()
    const sql = `delete from usersinfo where id in (${id})`;
    // console.log('sql: ---', sql)
    const data = await this.ctx.app.mysql.query(sql)
    if (data.affectedRows) {
      return { id: data.affectedRows, code: 0, msg: 'success' };
    } else {
      return { code: -1, msg: 'id不存在' };
    }
  }

  async search(keywords) {
    const sql = `select * from usersinfo where username like '%${keywords}%';`
    const data = await this.ctx.app.mysql.query(sql)
    if (data.length) {
      return { data, code: 0, msg: 'success' };
    } else {
      return { msg: '条件不成立' }
    }
  }

  async findAll() {
    const sql = `select * from usersinfo`;
    const data = await this.ctx.app.mysql.query(sql)
    return { code: 0, msg: 'success', data };
  }

  async findId(id) {
    const sql = `select * from usersinfo where id in (${id})`;
    const data = await this.ctx.app.mysql.query(sql)
    return { code: 0, msg: 'success', data };
  }
}

module.exports = HomeService